/**
 * Copyright 2005 The Apache Software Foundation
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package com.edi.db.dialect;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.List;
import java.util.Set;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.edi.db.EdiDb;
import com.edi.db.table.FieldEntity;
import com.edi.db.table.TableEntity;
import com.edi.db.type.NullableType;

public class MySQLDialect extends Dialect {
	private static final Log log = LogFactory.getLog(MySQLDialect.class);
	public MySQLDialect(){
		super(); 
		registColumnTypes();
	}   
	
	/**
	 * 注册mysql数据类别
	 *
	 */
	private void registColumnTypes(){
		registerColumnType( Types.BIT, "bit" );
		registerColumnType( Types.BIGINT, "bigint" );
		registerColumnType( Types.SMALLINT, "smallint" );
		registerColumnType( Types.TINYINT, "tinyint" );
		registerColumnType( Types.INTEGER, "integer" );
		registerColumnType( Types.CHAR, "char(1)" );
		registerColumnType( Types.FLOAT, "float" );
		registerColumnType( Types.DOUBLE, "double precision" );
		registerColumnType( Types.DATE, "date" );
		registerColumnType( Types.TIME, "time" );
		registerColumnType( Types.TIMESTAMP, "datetime" );
		registerColumnType( Types.VARBINARY, "longblob" );
		registerColumnType( Types.VARBINARY, 16777215, "mediumblob" );
		registerColumnType( Types.VARBINARY, 65535, "blob" );
		registerColumnType( Types.VARBINARY, 255, "tinyblob" );
		registerColumnType( Types.NUMERIC, "decimal($p,$s)" );
		registerColumnType( Types.BLOB, "longblob" ); 
		registerColumnType( Types.CLOB, "longtext" ); 
		registerColumnType( Types.VARCHAR, "longtext" ); 
		registerColumnType( Types.VARCHAR, 255, "varchar($l)" );
	}  
	public String getCurrentTimestampSelectString() {
		return "select now()";
	}
 
	public String translate(String eql) { 
		return null;
	}
 
	/**
	 * 获取保存sql，用于批量提交
	 */
	public  String getSavePreparedString(List fieldNameist, String  tableName){
		String sql="";
		try{
			sql="insert into "+tableName+" ("; 
			Iterator it =fieldNameist.iterator();
			while(it.hasNext()){
				String fieldName=(String)it.next(); 
				if(!it.hasNext())
					sql+="'"+fieldName+"') values (";
				else
					sql+="'"+fieldName+"',";
			}
			for(int i=0;i<fieldNameist.size();i++){
				if(i==fieldNameist.size()-1)
					sql+="?)";
				else
					sql+="?,";
				
			}
		}
		catch(Exception e){
			e.printStackTrace();
		}
		log.debug(" return sql ="+sql);
		return sql;
	}
	
	public  String getDeletePreparedString(TableEntity tableEntity){
		if(tableEntity==null||tableEntity.getTableName()==null||"".equals(tableEntity.getTableName()))
			throw new RuntimeException("表名为空！");
		String sql = "";
		 
		try {
			sql = "delete " + tableEntity.getTableName() + " where ";
			List<String> keys=tableEntity.getPrimarykey(); 
			Iterator<String> keyIt=keys.iterator();
			while(keyIt.hasNext()){
				String key=keyIt.next();
				sql+= key + " = ? ";
				if(keyIt.hasNext())
					sql+=" and "+ key + " = ? ";
			}  
		} catch (Exception e) {
			e.printStackTrace();

		}
		log.debug(" return sql =" + sql);
		return sql;
	}  
	public String ifExitTable(String tableName){
		String sql="";
		sql="SHOW TABLES LIKE '"+tableName+"'";
		return sql;
	}
	public  String createTable(TableEntity tableEntity){
		String sql="";
		try{
			sql="CREATE TABLE "+tableEntity.getTableName()+" (";
			Hashtable hashTable=tableEntity.getFieldEntitys();
			Set set=hashTable.keySet();
			Iterator it=set.iterator();
			while(it.hasNext()){
				FieldEntity fieldEntity=(FieldEntity)it.next();
				if(fieldEntity==null)
					continue;
				sql+= fieldEntity.getFieldType().getName()+" ";
				String columnType=getTypeName(fieldEntity.getFieldType().sqlType(),fieldEntity.getSize(),fieldEntity.getPrecision(),fieldEntity.getScale()); 
					sql+=columnType;
				if(!fieldEntity.isNullable())
					sql+=" not null";
				if(fieldEntity.getDefaultValue()!=null&&!"".equals(fieldEntity.getDefaultValue()))
					sql+=" default '"+fieldEntity.getDefaultValue()+"'";
				if(fieldEntity.isIdentify())
					sql+=" PRIMARY KEY ";
				if(it.hasNext())
					sql+=",";
			}
			
		}
		catch(Exception e){
			e.printStackTrace();
		}
		return sql;
	}
	/**
	 * 返回删除表列的方言语句
	 */
	public  String dropColumn(String tableName,List fieldList){
		String sql="";
		try{
			sql=" alter table '"+tableName+"'";
			Iterator it=fieldList.iterator();
			while(it.hasNext()){
				FieldEntity fieldEntity=(FieldEntity)it.next(); 
				sql+="DROP column '"+fieldEntity.getFieldName()+"'";
				if(it.hasNext())
					sql+=","; 
			}		
		}catch(Exception e){
			e.printStackTrace();
		} 
		log.debug("sql "+sql);
		return sql;
	}
	/**
	 * 返回添加表列的方言语句
	 */
	public  String addColumn(String tableName,List fieldList){
		String sql="";
		try{
			sql="alter table '"+tableName+"'";
			Iterator it=fieldList.iterator();
			while(it.hasNext()){
				FieldEntity fieldEntity=(FieldEntity)it.next(); 
				String columnType=getTypeName(fieldEntity.getFieldType().sqlType(),fieldEntity.getSize(),fieldEntity.getPrecision(),fieldEntity.getScale());
				sql+="add column '"+fieldEntity.getFieldName()+"' "+columnType;
				if(it.hasNext())
					sql+=",";
			} 
		}catch(Exception e){
			e.printStackTrace();
		}
		log.debug(" sql ="+sql);
		return sql;
	}
	/**
	 * 返回修改列的方言语句
	 */
	public  String modifyColumn(String tableName,List fieldList){
		String sql="";
		try{
			sql=" ALTER TABLE '"+tableName+"'";
			Iterator it=fieldList.iterator();
			while(it.hasNext()){
				FieldEntity fieldEntity=(FieldEntity)it.next(); 
				sql+="change '"+tableName+"' '"+tableName+"' ";
				if(fieldEntity.getFieldType()!=null){
					String columnType=getTypeName(fieldEntity.getFieldType().sqlType(),fieldEntity.getSize(),fieldEntity.getPrecision(),fieldEntity.getScale());
					sql+= columnType;
				}
				if(!fieldEntity.isNullable())
					sql+="  not null";
				if(it.hasNext())
					sql+=",";
			}
			
		}catch(Exception e){
			e.printStackTrace();
		} 
		log.debug("sql ="+sql);
		return sql; 
	}

	@Override
	public boolean isEqualType(NullableType type1, NullableType type2) {
		return type1.sqlType() == type2.sqlType();
	}

	@Override
	public NullableType getSqlType(int type,int precesion,int scale) {
		NullableType rType=EdiDb.STRING;
		switch (type){ 
		case Types.LONGVARCHAR:
			rType=EdiDb.LONG;
			break; 
		case Types.BINARY:
			rType=EdiDb.BINARY;
			break;
		case Types.BLOB:
			rType=EdiDb.BLOB;
			break;
		case Types.BOOLEAN:
			rType=EdiDb.BOOLEAN;
			break;
		case Types.CHAR:
			rType=EdiDb.CHARACTER;
			break;
		case Types.CLOB:
			rType=EdiDb.CLOB;
			break;
		case Types.DATE:
			rType=EdiDb.DATE;
			break;
		case Types.DECIMAL:
			rType=EdiDb.BIG_DECIMAL;
			break;
		case Types.DOUBLE:
			rType=EdiDb.DOUBLE;
			break;
		case Types.FLOAT:
			rType=EdiDb.FLOAT;
			break;
		case Types.INTEGER:
			rType=EdiDb.INTEGER;
			break;
		case Types.TIME:
			rType=EdiDb.TIME;
			break;
		case Types.TIMESTAMP:
			rType=EdiDb.TIMESTAMP;
			break;
		case Types.VARCHAR:
			rType=EdiDb.STRING;
			break;
		case Types.VARBINARY:
			rType=EdiDb.BINARY;  
			break;
		case Types.NUMERIC:
			rType=EdiDb.BIG_INTEGER;
			break; 
		case Types.TINYINT:
			rType=EdiDb.BOOLEAN;
		}
		return rType;
	}

	@Override
	public List<String> getPrimaryKeys(Connection conn, String tableName)
			throws SQLException {
		List<String> primaryKeys = new ArrayList<String>();
		DatabaseMetaData dbmeta = conn.getMetaData();

		if (!tableName.equalsIgnoreCase(EdiDb.TABLE_EDI_SYSTEMP)) {
			ResultSet keysRsultSet = null;
			try{
			// 获得主键信息
				keysRsultSet = dbmeta.getPrimaryKeys(null, null, tableName);
			while (keysRsultSet.next()) {
				primaryKeys
						.add(keysRsultSet.getString("PK_NAME").toLowerCase());
			}
			}finally{
				if (keysRsultSet != null){
					keysRsultSet.close();
				}
			}
		}
		return primaryKeys;
	}
	
}

